﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BaseCoder.Utility;
using System.IO;
using System.Text.RegularExpressions;
using System.Configuration;

namespace BaseCoder.DALCoder
{
    public class DALCreate
    {
        private ProjectConfig config = null;
        public DALCreate(ProjectConfig config)
        {
            this.config = config;
        }
        private StringBuilder columnsString = null;
        public void CreateDAL()
        {
            Table[] tables = DBHelper.GetTables(config.ConectionString);
            if (tables.Length <= 0) return;
            for (int i = 0; i < tables.Length; i++)
            {
                CreateDALByTable(config.ConectionString, tables[i]);
                Console.WriteLine(tables[i].Name + " DAL数据操作类生成结束");
            }
        }
        public void CreateDALByTable(string connectionString, Table table)
        {
            columnsString = new StringBuilder();
            string dalName = DALFileName(table.Name);
            string modelName = (dalName.Substring(0, 1).ToLower() + dalName.Substring(1)).Replace("DAL", "");

            if (ConfigurationManager.AppSettings["DALCover"] == "0" && File.Exists(config["DALSavePath"] + dalName + ".cs"))
            {
                Console.WriteLine(dalName + " 已存在并且不可覆盖");
                return;
            }
            string DBConnetionName = config["DBConnetionName"];
            TableColumn[] columns = DBHelper.GetColumns(connectionString, table.Name);
            if (columns.Length <= 0) return;
            StringBuilder sb = new StringBuilder();
            StringBuilder tempsb = new StringBuilder();
            sb.Append(GetDescription(table.Name, table.Description));
            sb.Append(GetNamespace());
            sb.AppendLine("namespace " + config["DALNamespace"] + "");
            sb.AppendLine("{");
            if (!string.IsNullOrEmpty(table.Description))
            {
                sb.AppendLine("     /// <summary>");
                sb.AppendLine("     /// " + FormatDescrition(table.Description) + "");
                sb.AppendLine("     /// </summary>");
            }
            sb.Append("     public class " + dalName + "" + FactoryHelper.Inherit(config["isFactory"], dalName) + "\n");
            sb.Append("     {\n");
            /*各种数据库操作方法 Start*/

            #region 添加数据的方法
            sb.Append("         #region Add "+table.Name+"\n");
            sb.Append("         /// <summary>\n");
            sb.Append("         /// Add  " + table.Name + "\n");
            sb.Append("         /// </summary>\n");
            sb.Append("         /// <param name=\"" + modelName + "\">" + table.Name + " model</param>\n");
            sb.Append("         /// <returns>Primary Key Id</returns>\n");
            sb.Append("         public int Insert(" + table.Name + " " + modelName + ")\n");
            sb.Append("         {\n");
            sb.AppendLine("             StringBuilder sql = new StringBuilder();");
            sb.AppendLine("             sql.Append(\"Insert into " + table.Name + " values(\");");
            for (int i = 0; i < columns.Length; i++)
            {
                if (columns[i].IsPrimaryKey)
                {
                    columnsString.Append(columns[i].ColName);
                    if (i < columns.Length - 1) columnsString.Append(",");
                    continue;
                }
                tempsb.Append("@" + columns[i].ColName);
                if (i < columns.Length - 1) tempsb.Append(",");

                columnsString.Append(columns[i].ColName);
                if (i < columns.Length - 1) columnsString.Append(",");
            }
            sb.AppendLine("             sql.Append(\"" + tempsb.ToString() + "\");");
            sb.Append("             sql.Append(\");Select Scope_Identity()\");\n");
            sb.AppendLine("              SqlParameter[] parameters = {");
            tempsb.Remove(0, tempsb.Length);
            int hasPrimaryKey = 0;
            for (int i = 0; i < columns.Length; i++)
            {
                if (columns[i].IsPrimaryKey)
                {
                    hasPrimaryKey = 1;
                    continue;
                }
                sb.Append("                 new SqlParameter(\"@" + columns[i].ColName + "\", SqlDbType." + DBHelper.GetSqlDbType(columns[i].DbType) + ", " + columns[i].ColLength + ")");
                tempsb.AppendLine("             parameters[" + (i - hasPrimaryKey) + "].Value = " + modelName + "." + columns[i].ColName + ";");
                if (i < columns.Length - 1) sb.Append(",\n");
            }
            sb.AppendLine("\n              };");
            sb.Append(tempsb.ToString());
            sb.Append("         return Convert.ToInt32(SqlHelper.ExecuteScalar(DBConnection.GetConnection(\"" + DBConnetionName + "\"), CommandType.Text, sql.ToString(),parameters));\n");
            sb.Append("         }\n");
            sb.Append("          #endregion\n");
            sb.Append("\n");
            tempsb.Remove(0, tempsb.Length);
            #endregion

            #region 编辑数据
            sb.AppendLine("         #region Edit "+table.Name);
            sb.AppendLine("         /// <summary>");
            sb.AppendLine("         /// Edit " + table.Name);
            sb.AppendLine("         /// </summary>");
            sb.AppendLine("         /// <param name=\"model\">" + table.Name + " model</param>");
            sb.AppendLine("         /// <returns>edit result (true/false)</returns>");
            sb.AppendLine("         public bool Edit(" + table.Name + " " + modelName + ")");
            sb.AppendLine("         {");
            sb.AppendLine("             StringBuilder sql = new StringBuilder();");
            sb.AppendLine("             sql.Append(\"Update " + table.Name + " Set \");");

            for (int i = 0; i < columns.Length; i++)
            {
                if (columns[i].IsPrimaryKey)
                {
                    tempsb.Append(" Where " + columns[i].ColName + "=@" + columns[i].ColName);
                    continue;
                }
                if (!columns[i].ColName.Equals("InsertUserId", StringComparison.CurrentCultureIgnoreCase)
                    && !columns[i].ColName.Equals("InsertUserName", StringComparison.CurrentCultureIgnoreCase)
                    && !columns[i].ColName.Equals("InsertTime", StringComparison.CurrentCultureIgnoreCase)
                    && !columns[i].ColName.Equals("IsDeleted", StringComparison.CurrentCultureIgnoreCase)
                    )
                {
                    sb.AppendLine("             sql.Append(\"" + columns[i].ColName + "=@" + columns[i].ColName + " " + ((i < columns.Length - 1) ? "," : "") + "\");");
                }

            }
            sb.AppendLine("             sql.Append(\" " + tempsb.ToString() + "\");");
            sb.AppendLine("             SqlParameter[] parameters = {");
            tempsb.Remove(0, tempsb.Length);
            int tempInt = 0;
            for (int i = 0; i < columns.Length; i++)
            {
                if (!columns[i].ColName.Equals("InsertUserId", StringComparison.CurrentCultureIgnoreCase)
                   && !columns[i].ColName.Equals("InsertUserName", StringComparison.CurrentCultureIgnoreCase)
                   && !columns[i].ColName.Equals("InsertTime", StringComparison.CurrentCultureIgnoreCase)
                   && !columns[i].ColName.Equals("IsDeleted", StringComparison.CurrentCultureIgnoreCase)
                   )
                {
                    sb.Append("                 new SqlParameter(\"@" + columns[i].ColName + "\", SqlDbType." + DBHelper.GetSqlDbType(columns[i].DbType) + ", " + columns[i].ColLength + ")");
                    tempsb.AppendLine("             parameters[" + tempInt + "].Value = " + modelName + "." + columns[i].ColName + ";");
                    tempInt++;
                    if (i < columns.Length - 1) sb.Append(",\n");
                }

            }
            sb.AppendLine("\n              };");
            sb.AppendLine(tempsb.ToString());
            sb.AppendLine("             return SqlHelper.ExecuteNonQuery(DBConnection.GetConnection(\"" + DBConnetionName + "\"), CommandType.Text, sql.ToString(),parameters) > 0 ? true : false;");
            sb.AppendLine("         }");
            sb.AppendLine("         #endregion");
            #endregion


            #region 修改字段
            sb.Append("         #region Edit Field\n");
            sb.Append("         /// <summary>\n");
            sb.Append("         /// Edit Field\n");
            sb.Append("         /// </summary>\n");
            sb.Append("         /// <param name=\"fields\">data items</param>\n");
            sb.Append("         /// <param name=\"where\">update condition</param>\n");
            sb.Append("         /// <returns>Edit Result(true/false)</returns>\n");
            sb.Append("         public bool Update(string field, string where)\n");
            sb.Append("         {\n");
            sb.Append("             string sql = string.Format(\"UPDATE " + table.Name + " SET {0}  WHERE {1} \", field, where);\n");
            sb.Append("             return SqlHelper.ExecuteNonQuery(DBConnection.GetConnection(\"" + DBConnetionName + "\"), CommandType.Text, sql) > 0 ? true : false;\n");
            sb.Append("         }\n");
            sb.Append("         #endregion\n");
            sb.Append("\n");
            #endregion

            #region 删除数据
            sb.Append("         #region Delete "+table.Name+"\n");
            sb.Append("         /// <summary>\n");
            sb.Append("         /// Delete " + table.Name + "\n");
            sb.Append("         /// </summary>\n");
            sb.Append("         /// <param name=\"where\">Delete Condition</param>\n");
            sb.Append("         /// <returns>Delete Result (true/false)</returns>\n");
            sb.Append("         public bool Delete(string where)\n");
            sb.Append("         {\n");
            sb.Append("             string sql = \"UPDATE " + table.Name + " SET isDeleted='True' Where \" + where;\n");
            sb.Append("             return SqlHelper.ExecuteNonQuery(DBConnection.GetConnection(\"" + DBConnetionName + "\"), CommandType.Text, sql) > 0 ? true : false;\n");
            sb.Append("          }\n");
            sb.Append("         #endregion\n");

            #endregion

            #region 获得一个数据实体

            sb.Append("         #region Get A "+table.Name+" Model\n");
            sb.Append("         /// <summary>\n");
            sb.Append("         /// get a " + table.Name + " model\n");
            sb.Append("         /// </summary>\n");
            sb.Append("         /// <param name=\"where\"> query terms  exclude 'where'</param>\n");
            sb.Append("         /// <returns></returns>\n");
            sb.Append("         public " + table.Name + " Get(string where)\n");
            sb.Append("         {\n");
            sb.Append("             " + table.Name + " " + modelName + " = null;\n");
            sb.Append("             string sql = \"SELECT top 1 * FROM " + table.Name + " Where \" + where;\n");
            sb.Append("             DataSet ds = SqlHelper.ExecuteDataset(DBConnection.GetConnection(\"" + DBConnetionName + "\"), CommandType.Text, sql);\n");
            sb.Append("             if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)\n");
            sb.Append("             {\n");
            sb.Append("                 " + modelName + " = new " + table.Name + "();\n");
            string keyId = string.Empty;
            for (int i = 0; i < columns.Length; i++)
            {
                if (columns[i].IsPrimaryKey) keyId = columns[i].ColName;
                sb.Append("                 " + modelName + "." + columns[i].ColName + " = FieldConvert." + DBHelper.GetFormatDBTypeFn(columns[i].DbType) + "(ds.Tables[0].Rows[0][\"" + columns[i].ColName + "\"]);\n");
            }

            sb.Append("             }\n");
            sb.Append("             ds.Dispose();\n");
            sb.Append("             return " + modelName + ";\n");
            sb.Append("         }\n");
            sb.Append("         #endregion\n");
            sb.Append("\n");

            #endregion

            #region 获得全部数据
            sb.Append("         #region Get All "+table.Name+" Data Model\n");
            sb.Append("         /// <summary>\n");
            sb.Append("         ///  Get All " + table.Name + " Data Model\n");
            sb.Append("         /// </summary>\n");
            sb.Append("         /// <param name=\"where\">where</param>\n");
            sb.Append("         /// <param name=\"orderBy\">order fields</param>\n");
            sb.Append("         /// <returns></returns>\n");
            sb.Append("         public IList<" + table.Name + "> GetAllList( string where=\"\",string orderBy=\"\")\n");
            sb.Append("         {\n");
            sb.Append("             IList<" + table.Name + "> list = null;\n");
            sb.Append("             string sql = string.Format(\"select " + columnsString + " from " + table.Name + " \" + (string.IsNullOrEmpty(where) ? \"\" : \"where\") + \" {0} {1}\",  where, orderBy);;\n");
            sb.Append("             DataSet ds = SqlHelper.ExecuteDataset(DBConnection.GetConnection(\"" + DBConnetionName + "\"), CommandType.Text, sql);\n");

            sb.Append("             if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)\n");
            sb.Append("             {\n");
            sb.Append("                 list = new List<" + table.Name + ">();\n");
            sb.Append("                 for (int i = 0; i < ds.Tables[0].Rows.Count; i++)\n");
            sb.Append("                 {\n");
            sb.Append("                     " + table.Name + " " + modelName + " = new " + table.Name + "();\n");
            for (int i = 0; i < columns.Length; i++)
            {
                sb.Append("                 " + modelName + "." + columns[i].ColName + " = FieldConvert." + DBHelper.GetFormatDBTypeFn(columns[i].DbType) + "(ds.Tables[0].Rows[i][\"" + columns[i].ColName + "\"]);\n");
            }
            sb.Append("                     list.Add(" + modelName + ");\n");
            sb.Append("                 }\n");
            sb.Append("             }\n");
            sb.Append("             ds.Dispose();\n");
            sb.Append("             return list;\n");
            sb.Append("         }\n");
            sb.Append("         #endregion\n");
            sb.Append("\n");
            #endregion

            #region 分页数据查询
            sb.Append("         #region Paging inquires\n");
            sb.Append("         /// <summary>\n");
            sb.Append("         /// Paging inquires\n");
            sb.Append("         /// </summary>\n");
            sb.Append("         /// <param name=\"record\">tota records</param>\n");
            sb.Append("         /// <param name=\"curPage\">current page</param>\n");
            sb.Append("         /// <param name=\"pageSize\">pagesize </param>\n");
            sb.Append("         /// <param name=\"orderBy\">order fields</param>\n");
            sb.Append("         /// <param name=\"where\">where</param>\n");
            sb.Append("         /// <returns></returns>\n");
            sb.Append("         public IList<" + table.Name + "> GetList(out int record, int curPage, int pageSize, string orderBy, string where)\n");
            sb.Append("         {\n");
            sb.Append("             IList<" + table.Name + "> list = null;\n");
            sb.Append("             DataSet ds = Searcher.ExecuteSearch(out record, DBConnection.GetConnection(\"" + DBConnetionName + "\"),\n");
            sb.Append("                 \"" + table.Name + "\",\"" + keyId + "\",curPage, pageSize,\"" + columnsString + "\",\n");
            sb.Append("                 orderBy,where);\n");
            sb.Append("             if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)\n");
            sb.Append("             {\n");
            sb.Append("                 list = new List<" + table.Name + ">();\n");
            sb.Append("                 for (int i = 0; i < ds.Tables[0].Rows.Count; i++)\n");
            sb.Append("                 {\n");
            sb.Append("                     " + table.Name + " " + modelName + " = new " + table.Name + "();\n");
            for (int i = 0; i < columns.Length; i++)
            {
                sb.Append("                 " + modelName + "." + columns[i].ColName + " = FieldConvert." + DBHelper.GetFormatDBTypeFn(columns[i].DbType) + "(ds.Tables[0].Rows[i][\"" + columns[i].ColName + "\"]);\n");
            }
            sb.Append("                     list.Add(" + modelName + ");\n");
            sb.Append("                 }\n");
            sb.Append("             }\n");
            sb.Append("             ds.Dispose();\n");
            sb.Append("             return list;\n");
            sb.Append("         }\n");
            sb.Append("         #endregion\n");
            sb.Append("\n");
            #endregion

            /*各种数据库操作方法 End*/
            sb.Append("     }\n");
            sb.Append("}\n");

            if (File.Exists(config["DALSavePath"] + dalName + ".cs")) File.Delete(config["DALSavePath"] + dalName + ".cs");
            FileStream fs = new FileStream(config["DALSavePath"] + dalName + ".cs", FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite);
            StreamWriter sw = new StreamWriter(fs);
            sw.Flush();
            sw.BaseStream.Seek(0, SeekOrigin.End);
            sw.WriteLine(sb.ToString());
            sw.Flush();
            sw.Close();
        }

        public string DALFileName(string tableName)
        {
            if (string.IsNullOrEmpty(tableName)) throw new Exception("表名不正确！");
            if (tableName.IndexOf("_") < 0) return tableName + "DAL";
            string fileName = "";
            string[] tableNames = tableName.Split('_');
            if (tableNames.Length > 3)
            {
                for (int i = 2; i < tableNames.Length; i++)
                {
                    fileName += tableNames[i];
                }
            }
            else if (tableNames.Length > 2)
            {
                fileName = tableNames[2];
            }
            else
            {
                fileName = tableNames[1];
            }

            return fileName + "DAL";
        }

        public string GetDescription(string tableName, string tableDescription)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("//----------------------------------------------------------------------------------------------------");
            sb.AppendLine("//Name:  " + tableName + "");
            sb.AppendLine("//Function:	" + FormatDescrition(tableDescription) + "" + " database operation");
            sb.AppendLine("//Author:	BaseCoder");
            sb.AppendLine("//Date:    " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "");
            sb.AppendLine("//----------------------------------------------------------------------------------------------------");
            sb.AppendLine("//Change History:");
            sb.AppendLine(" // Date		Who		Changes Made");
            sb.AppendLine("//----------------------------------------------------------------------------------------------------");
            sb.AppendLine("//" + DateTime.Now.ToString("yyyy-MM-dd") + "	BaseCoder		create cs file");
            sb.AppendLine("//----------------------------------------------------------------------------------------------------\n");

            return sb.ToString();
        }
        public string GetNamespace()
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("using System;");
            sb.AppendLine("using System.Collections.Generic;");
            sb.AppendLine("using System.Linq;");
            sb.AppendLine("using System.Data;");
            sb.AppendLine("using DBUtility;");
            sb.AppendLine("using System.Data.SqlClient;");
            sb.AppendLine("using " + config["ModelNamespace"] + ";");
            if (config["IsFactory"] == "1")
            {
                sb.AppendLine("using " + config["IDAL"] + ";");
            }
            sb.AppendLine("using System.Text;\r");
            sb.AppendLine("");
            return sb.ToString();
        }
        public string FormatDescrition(string desp)
        {
            Regex regex = new Regex("\r\n", RegexOptions.IgnoreCase);
            return regex.Replace(desp, "");
        }
    }
}
